1 Imports System.Data.SqlClient
2 Public Class frmInstallment_Hostel
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New SqlConnection(cs)
7 con.Open()
8 Dim sql As String = ("SELECT MAX(IH_ID) FROM Installment_Hostel")
9 cmd = New SqlCommand(sql)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtID.Text = Num.ToString
17 End If
18 cmd.Dispose()
19 con.Close()
20 con.Dispose()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25
26 Sub Reset()
27 txtInstallment.Text = ""
28 cmbHostel.SelectedIndex = -1
29 cmbClass.SelectedIndex = -1
30 cmbSchoolName.SelectedIndex = -1
31 txtSearchByClass.Text = ""
32 txtCharges.Text = ""
33 txtInstallment.Focus()
34 btnSave.Enabled = True
35 btnUpdate.Enabled = False
36 btnDelete.Enabled = False
37 Getdata()
38 auto()
39 End Sub
40 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
41 Me.Close()
42 End Sub
43
44 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
45
46 If Len(Trim(txtInstallment.Text)) = 0 Then
47 MessageBox.Show("Please enter installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
48 txtInstallment.Focus()
49 Exit Sub
50 End If
51 If Len(Trim(cmbHostel.Text)) = 0 Then
52 MessageBox.Show("Please select hostel", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
53 cmbHostel.Focus()
54 Exit Sub
55 End If
56 If Len(Trim(cmbSchoolName.Text)) = 0 Then
57 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
58 cmbSchoolName.Focus()
59 Exit Sub
60 End If
61 If Len(Trim(cmbClass.Text)) = 0 Then
62 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
63 cmbClass.Focus()
64 Exit Sub
65 End If
66 If Len(Trim(txtCharges.Text)) = 0 Then
67 MessageBox.Show("Please enter charges", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
68 txtCharges.Focus()
69 Exit Sub
70 End If
71 Try
72 con = New SqlConnection(cs)
73 con.Open()
74 Dim ct As String = "select Installment,HostelID,SchoolID,ClassName from Installment_Hostel,HostelInfo,SchoolInfo,Class where Class.Classname=Installment_Hostel.Class and HostelInfo.HI_ID=Installment_Hostel.HostelID and SchoolInfo.S_ID=Installment_Hostel.SchoolID and Installment=@d1 and HostelID=@d2 and SchoolID=@d3 and ClassName=@d4"
75 cmd = New SqlCommand(ct)
76 cmd.Parameters.AddWithValue("@d1", txtInstallment.Text)
77 cmd.Parameters.AddWithValue("@d2", txtHostelID.Text)
78 cmd.Parameters.AddWithValue("@d3", txtSchoolID.Text)
79 cmd.Parameters.AddWithValue("@d4", cmbClass.Text)
80 cmd.Connection = con
81 rdr = cmd.ExecuteReader()
82 If rdr.Read() Then
83 MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
84 If (rdr IsNot Nothing) Then
85 rdr.Close()
86 End If
87 Return
88 End If
89 con = New SqlConnection(cs)
90 con.Open()
91 Dim cb As String = "insert into Installment_Hostel(IH_ID,Installment,Charges,HostelID,SchoolID,Class) VALUES (" & txtID.Text & ",@d1,@d2," & txtHostelID.Text & ",@d3,@d4)"
92 cmd = New SqlCommand(cb)
93 cmd.Connection = con
94 cmd.Parameters.AddWithValue("@d1", txtInstallment.Text)
95 cmd.Parameters.AddWithValue("@d2", txtCharges.Text)
96 cmd.Parameters.AddWithValue("@d3", txtSchoolID.Text)
97 cmd.Parameters.AddWithValue("@d4", cmbClass.Text)
98 cmd.ExecuteReader()
99 con.Close()
100 LogFunc(lblUser.Text, "added the new Installment '" & txtInstallment.Text & "' for hostel '" & cmbHostel.Text & "'")
101 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
102 Getdata()
103 Autocomplete()
104 auto()
105 Catch ex As Exception
106 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
107 End Try
108 End Sub
109
110 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
111 Try
112 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
113 DeleteRecord()
114 End If
115 Catch ex As Exception
116 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
117 End Try
118 End Sub
119 Private Sub DeleteRecord()
120
121 Try
122 con.Open()
123 Dim cl As String = "select InstallmentID from Installment_Hostel,HostelFeePayment where Installment_Hostel.IH_ID=HostelFeePayment.InstallmentID and InstallmentID=@d1"
124 cmd = New SqlCommand(cl)
125 cmd.Connection = con
126 cmd.Parameters.AddWithValue("@d1", txtID.Text)
127 rdr = cmd.ExecuteReader()
128 If rdr.Read Then
129 MessageBox.Show("Unable to delete..Already in use in Hostel Fee Payment", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
130 If Not rdr Is Nothing Then
131 rdr.Close()
132 End If
133 Exit Sub
134 End If
135 Dim RowsAffected As Integer = 0
136 con = New SqlConnection(cs)
137 con.Open()
138 Dim cq As String = "delete from Installment_Hostel where IH_ID=@d1"
139 cmd = New SqlCommand(cq)
140 cmd.Connection = con
141 cmd.Parameters.AddWithValue("@d1", txtID.Text)
142 RowsAffected = cmd.ExecuteNonQuery()
143 If RowsAffected > 0 Then
144 LogFunc(lblUser.Text, "deleted the Installment '" & txtInstallment.Text & "' of hostel '" & cmbHostel.Text & "'")
145 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
146 Getdata()
147 Reset()
148 Autocomplete()
149 Else
150 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
151 Reset()
152 End If
153 If con.State = ConnectionState.Open Then
154 con.Close()
155
156 End If
157 Catch ex As Exception
158 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
159 End Try
160 End Sub
161 Sub fillCombo()
162 Try
163 con = New SqlConnection(cs)
164 con.Open()
165 adp = New SqlDataAdapter()
166 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(Hostelname) FROM HostelInfo", con)
167 ds = New DataSet("ds")
168 adp.Fill(ds)
169 dtable = ds.Tables(0)
170 cmbHostel.Items.Clear()
171 For Each drow As DataRow In dtable.Rows
172 cmbHostel.Items.Add(drow(0).ToString())
173 Next
174 Catch ex As Exception
175 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
176 End Try
177 End Sub
178 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
179
180 If Len(Trim(txtInstallment.Text)) = 0 Then
181 MessageBox.Show("Please enter installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
182 txtInstallment.Focus()
183 Exit Sub
184 End If
185 If Len(Trim(cmbHostel.Text)) = 0 Then
186 MessageBox.Show("Please select hostel", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
187 cmbHostel.Focus()
188 Exit Sub
189 End If
190 If Len(Trim(cmbSchoolName.Text)) = 0 Then
191 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
192 cmbSchoolName.Focus()
193 Exit Sub
194 End If
195 If Len(Trim(cmbClass.Text)) = 0 Then
196 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
197 cmbClass.Focus()
198 Exit Sub
199 End If
200 If Len(Trim(txtCharges.Text)) = 0 Then
201 MessageBox.Show("Please enter charges", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
202 txtCharges.Focus()
203 Exit Sub
204 End If
205 Try
206 con = New SqlConnection(cs)
207 con.Open() '
208 Dim cb As String = "Update Installment_Hostel set Installment=@d1,Charges=@d2,HostelID=" & txtHostelID.Text & ",SchoolID=@d3,Class=@d4 where IH_ID=" & txtID.Text & ""
209 cmd = New SqlCommand(cb)
210 cmd.Connection = con
211 cmd.Parameters.AddWithValue("@d1", txtInstallment.Text)
212 cmd.Parameters.AddWithValue("@d2", txtCharges.Text)
213 cmd.Parameters.AddWithValue("@d3", txtSchoolID.Text)
214 cmd.Parameters.AddWithValue("@d4", cmbClass.Text)
215 cmd.ExecuteReader()
216 con.Close()
217 LogFunc(lblUser.Text, "updated the Installment '" & txtInstallment.Text & "' of hostel '" & cmbHostel.Text & "'")
218 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
219 btnUpdate.Enabled = False
220 Getdata()
221 Autocomplete()
222 Catch ex As Exception
223 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
224 End Try
225 End Sub
226 Public Sub Getdata()
227 Try
228 con = New SqlConnection(cs)
229 con.Open()
230 cmd = New SqlCommand("SELECT RTRIM(IH_ID), RTRIM(Installment),RTRIM(HostelID),RTRIM(HostelName),RTRIM(SchoolID),RTRIM(Schoolname),RTRIM(ClassName),RTRIM(Charges) from Installment_Hostel,HostelInfo,SchoolInfo,Class where Class.Classname=Installment_Hostel.Class and HostelInfo.HI_ID=Installment_Hostel.HostelID and SchoolInfo.S_ID=Installment_Hostel.SchoolID order by HostelName,Installment", con)
231 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
232 dgw.Rows.Clear()
233 While (rdr.Read() = True)
234 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7))
235 End While
236 con.Close()
237 Catch ex As Exception
238 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
239 End Try
240 End Sub
241 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
242 Reset()
243 End Sub
244
245
246 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
247 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
248 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
249 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
250 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
251 End If
252 Dim b As Brush = SystemBrushes.ControlText
253 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
254
255 End Sub
256
257 Private Sub frmCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
258 Getdata()
259 fillCombo()
260 fillClass()
261 fillSchool()
262 Autocomplete()
263 End Sub
264 Sub Autocomplete()
265 Try
266 con = New SqlConnection(cs)
267 con.Open()
268 cmd = New SqlCommand("SELECT Distinct Installment from Installment_Hostel", con)
269 ds = New DataSet()
270 adp = New SqlDataAdapter(cmd)
271 adp.Fill(ds, "Installment")
272 Dim col As AutoCompleteStringCollection = New AutoCompleteStringCollection()
273 Dim i As Integer = 0
274 For i = 0 To ds.Tables(0).Rows.Count - 1
275 col.Add(ds.Tables(0).Rows(i)("Installment").ToString())
276 Next
277 txtInstallment.AutoCompleteSource = AutoCompleteSource.CustomSource
278 txtInstallment.AutoCompleteCustomSource = col
279 txtInstallment.AutoCompleteMode = AutoCompleteMode.Suggest
280 con.Close()
281 Catch ex As Exception
282 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
283 End Try
284 End Sub
285 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
286 Try
287 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
288 txtInstallment.Text = dr.Cells(1).Value.ToString()
289 txtID.Text = dr.Cells(0).Value.ToString()
290 txtHostelID.Text = dr.Cells(2).Value.ToString()
291 cmbHostel.Text = dr.Cells(3).Value.ToString()
292 txtSchoolID.Text = dr.Cells(4).Value.ToString()
293 cmbSchoolName.Text = dr.Cells(5).Value.ToString()
294 cmbClass.Text = dr.Cells(6).Value.ToString()
295 txtCharges.Text = dr.Cells(7).Value.ToString()
296 btnUpdate.Enabled = True
297 btnDelete.Enabled = True
298 btnSave.Enabled = False
299 Catch ex As Exception
300 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
301 End Try
302 End Sub
303
304
305 Private Sub txtFee_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtCharges.KeyPress
306 Dim keyChar = e.KeyChar
307
308 If Char.IsControl(keyChar) Then
309 'Allow all control characters.
310 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
311 Dim text = Me.txtCharges.Text
312 Dim selectionStart = Me.txtCharges.SelectionStart
313 Dim selectionLength = Me.txtCharges.SelectionLength
314
315 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
316
317 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
318 'Reject an integer that is longer than 16 digits.
319 e.Handled = True
320 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
321 'Reject a real number with two many decimal places.
322 e.Handled = False
323 End If
324 Else
325 'Reject all other characters.
326 e.Handled = True
327 End If
328 End Sub
329
330 Private Sub cmbHostel_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbHostel.SelectedIndexChanged
331 Try
332 con = New SqlConnection(cs)
333 con.Open()
334 cmd = con.CreateCommand()
335 cmd.CommandText = "SELECT HI_ID FROM HostelInfo where HostelName=@d1"
336 cmd.Parameters.AddWithValue("@d1", cmbHostel.Text)
337 rdr = cmd.ExecuteReader()
338 If rdr.Read() Then
339 txtHostelID.Text = rdr.GetValue(0)
340 End If
341 If (rdr IsNot Nothing) Then
342 rdr.Close()
343 End If
344 If con.State = ConnectionState.Open Then
345 con.Close()
346 End If
347 Catch ex As Exception
348 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
349 End Try
350 End Sub
351
352 Private Sub txtInstallment_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtInstallment.TextChanged
353 txtInstallment.Text = txtInstallment.Text.Trim()
354 End Sub
355
356 Private Sub cmbSchoolName_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSchoolName.SelectedIndexChanged
357 Try
358 con = New SqlConnection(cs)
359 con.Open()
360 cmd = con.CreateCommand()
361 cmd.CommandText = "SELECT S_ID FROM SchoolInfo where SchoolName=@d1"
362 cmd.Parameters.AddWithValue("@d1", cmbSchoolName.Text)
363 rdr = cmd.ExecuteReader()
364 If rdr.Read() Then
365 txtSchoolID.Text = rdr.GetValue(0)
366 End If
367 If (rdr IsNot Nothing) Then
368 rdr.Close()
369 End If
370 If con.State = ConnectionState.Open Then
371 con.Close()
372 End If
373 Catch ex As Exception
374 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
375 End Try
376 End Sub
377 Sub fillSchool()
378 Try
379 con = New SqlConnection(cs)
380 con.Open()
381 adp = New SqlDataAdapter()
382 adp.SelectCommand = New SqlCommand("SELECT distinct (SchoolName) FROM SchoolInfo", con)
383 ds = New DataSet("ds")
384 adp.Fill(ds)
385 dtable = ds.Tables(0)
386 cmbSchoolName.Items.Clear()
387 For Each drow As DataRow In dtable.Rows
388 cmbSchoolName.Items.Add(drow(0).ToString())
389 Next
390 Catch ex As Exception
391 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
392 End Try
393 End Sub
394 Sub fillClass()
395 Try
396 con = New SqlConnection(cs)
397 con.Open()
398 adp = New SqlDataAdapter()
399 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ClassName) FROM Class", con)
400 ds = New DataSet("ds")
401 adp.Fill(ds)
402 dtable = ds.Tables(0)
403 cmbClass.Items.Clear()
404 For Each drow As DataRow In dtable.Rows
405 cmbClass.Items.Add(drow(0).ToString())
406 Next
407 Catch ex As Exception
408 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
409 End Try
410 End Sub
411
412 Private Sub txtSearchByClass_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByClass.TextChanged
413 Try
414 con = New SqlConnection(cs)
415 con.Open()
416 cmd = New SqlCommand("SELECT RTRIM(IH_ID), RTRIM(Installment),RTRIM(HostelID),RTRIM(HostelName),RTRIM(SchoolID),RTRIM(Schoolname),RTRIM(Class),RTRIM(Charges) from Installment_Hostel,HostelInfo,SchoolInfo,Class where Class.Classname=Installment_Hostel.Class and HostelInfo.HI_ID=Installment_Hostel.HostelID and SchoolInfo.S_ID=Installment_Hostel.SchoolID and Installment_Hostel.Class like '" & txtSearchByClass.Text & "%' order by HostelName,Installment", con)
417 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
418 dgw.Rows.Clear()
419 While (rdr.Read() = True)
420 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7))
421 End While
422 con.Close()
423 Catch ex As Exception
424 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
425 End Try
426 End Sub
427
428 Private Sub Label4_Click(sender As System.Object, e As System.EventArgs) Handles Label4.Click
429
430 End Sub
431 End Class
